use rusqlite::{params, Connection, Result};
use serde::{Deserialize, Serialize};

const DB_PATH: &str = "./database/student.db";

#[derive(Debug, Serialize, Deserialize)]
pub struct Student {
    pub id: String,
    pub name: String,
    pub gender: String,
    pub score: i32,
}

pub fn get_connection() -> Result<Connection> {
    Connection::open(DB_PATH)
}

pub fn fetch_student(student_id: &str) -> Result<Option<Student>> {
    let conn = get_connection()?;
    let mut stmt = conn.prepare("SELECT id, name, gender, score FROM students WHERE id = ?1")?;

    let mut rows = stmt.query_map(params![student_id], |row| {
        Ok(Student {
            id: row.get(0)?,
            name: row.get(1)?,
            gender: row.get(2)?,
            score: row.get(3)?,
        })
    })?;

    if let Some(result) = rows.next() {
        result.map(Some)
    } else {
        Ok(None)
    }
}

pub fn insert_student(student: &Student) -> Result<()> {
    let conn = get_connection()?;
    conn.execute(
        "INSERT INTO students (id, name, gender, score) VALUES (?1, ?2, ?3, ?4)",
        params![student.id, student.name, student.gender, student.score],
    )?;
    Ok(())
}

pub fn fetch_all_students() -> Result<Vec<Student>> {
    let conn = get_connection()?;
    let mut stmt = conn.prepare("SELECT id, name, gender, score FROM students")?;

    let students = stmt
        .query_map([], |row| {
            Ok(Student {
                id: row.get(0)?,
                name: row.get(1)?,
                gender: row.get(2)?,
                score: row.get(3)?,
            })
        })?
        .collect::<Result<Vec<_>>>()?;

    Ok(students)
}

pub fn init_database() -> Result<()> {
    let conn = get_connection()?;

    conn.execute(
        "CREATE TABLE IF NOT EXISTS students (
            id TEXT PRIMARY KEY,
            name TEXT,
            gender TEXT,
            score INTEGER
        )",
        [],
    )?;

    let students = vec![
        ("2023001", "张三", "男", 88),
        ("2023002", "李四", "女", 92),
        ("2023003", "王五", "男", 76),
    ];

    for (id, name, gender, score) in students {
        conn.execute(
            "INSERT OR REPLACE INTO students (id, name, gender, score) VALUES (?1, ?2, ?3, ?4)",
            params![id, name, gender, score],
        )?;
    }

    Ok(())
}

#[allow(unused)]
#[cfg(test)]
mod tests {
    use super::*;

    #[test]
    fn test_init_database() {
        if let Err(e) =  init_database() {
            eprintln!("{}", e);
        }
    }
}